-- sync them, 1000 per transaction, to the new tables
CREATE PROCEDURE [dbo].[asi_SyncNetContacts] AS
BEGIN
    SET NOCOUNT ON
    DECLARE @isDone bit
    DECLARE @count int
    DECLARE @rowcount int
    DECLARE @totalCount int
    DECLARE @msg nvarchar(255)
    
    SET @count = 0
    SELECT @totalCount = COUNT(1)
      FROM Name c LEFT OUTER JOIN ContactMain d
        ON c.ID = d.SyncContactID
     WHERE d.SyncContactID IS NULL
     
    SET @msg = 'Total contact records to sync: ' + CAST(@totalCount AS nvarchar(12))
    RAISERROR (@msg, 0, 1) WITH NOWAIT;
    
    SET @isDone = 0
    WHILE @isDone = 0
    BEGIN
        BEGIN TRANSACTION
        UPDATE a
           SET LAST_FIRST = LAST_FIRST
          FROM Name a INNER JOIN (
                SELECT TOP 2000 c.ID
                  FROM Name c LEFT OUTER JOIN ContactMain d
                    ON c.ID = d.SyncContactID
                 WHERE d.SyncContactID IS NULL) b
            ON a.ID = b.ID

        SET @rowcount = @@ROWCOUNT
        IF @rowcount = 0
        BEGIN
            SET @isDone = 1
        END
        ELSE
        BEGIN
            SET @count = @count + @rowcount
            SET @msg = '   Contacts synced: ' + CAST(@count AS nvarchar(12))  
            RAISERROR (@msg, 0, 1) WITH NOWAIT;
        END
        COMMIT
    END

    -- fix up the problem where the company a person works for is in a later block than the
    -- person is.  e.g., the CO_ID > the ID
    INSERT INTO GroupMember
           (GroupMemberKey, GroupKey, MemberContactKey, IsActive, CreatedByUserKey, CreatedOn, UpdatedByUserKey, UpdatedOn)
    SELECT NewId(), d.InstituteGroupKey, b.ContactKey, 1, b.CreatedByUserKey, b.CreatedOn, b.CreatedByUserKey, b.CreatedOn
      FROM Name a inner join ContactMain b ON a.ID = b.ID
             inner join ContactMain c ON a.CO_ID = c.ID
           inner join Institute d ON c.ContactKey = d.ContactKey
           left outer join GroupMember e ON d.InstituteGroupKey = e.GroupKey AND b.ContactKey = e.MemberContactKey
     WHERE e.GroupKey IS NULL

    INSERT INTO GroupMemberDetail
           (GroupMemberDetailKey, GroupMemberKey, GroupKey, GroupRoleKey, EffectiveDate,
            CreatedByUserKey, CreatedOn, UpdatedByUserKey, UpdatedOn, GroupMemberStatusCode, IsActive)
    SELECT newid(), c.GroupMemberKey, c.GroupKey, f.GroupRoleKey, c.CreatedOn,
           c.CreatedByUserKey, c.CreatedOn, c.CreatedByUserKey, c.CreatedOn, f.DefaultMemberStatusCode, 1
      FROM GroupMain a
             inner join ContactTypeRef b ON a.GroupTypeKey = b.GroupTypeKey and b.ContactTypeDesc = 'Company'
           inner join GroupMember c ON a.GroupKey = c.GroupKey
           left outer join GroupMemberDetail d ON c.GroupMemberKey = d.GroupMemberKey
           inner join GroupTypeRole f on b.GroupTypeKey = f.GroupTypeKey AND f.IsDefault = 1
     WHERE d.GroupMemberKey IS NULL

    RAISERROR ('Sync complete', 0, 1) WITH NOWAIT;
    SET NOCOUNT OFF
END


GO